-- STANDARD SCRIPT
-- THE batch_insurance1_t TABLES IS UPDATED WITH THE SCORE FIELDS
--


-----------------------------------------------------
-- FUNCTION TO UPDATE BATCH ACCOUNT TABLE WITH SCORES


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_batch_insurance1_postscoring_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_count integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance1_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance1_t limit 1;

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,5,'START LOADING SCORES INTO batch_insurance1_t');


-- STEP 1 LOAD SCORES INTO batch_insurance1_t    

RAISE NOTICE 'START LOADING SCORES INTO batch_insurance1_t TABLE';


  UPDATE batch_insurance1_t
  SET
     commercial_score         = ba.commercial_score,
     scoring_code_commercial  = ba.scoring_code_commercial,
     tpl_score                = ba.tpl_score,
     scoring_code_tpl         = ba.scoring_code_tpl,
     medicaid_score           = ba.medicaid_score,
     scoring_code_medicaid    = ba.scoring_code_medicaid,
     medicare_score           = ba.medicare_score,
     scoring_code_medicare    = ba.scoring_code_medicare,
     emdeon_sent_date         = (case when ba.emdeon_commercial_flag = '1' then our_run_date
                                      when ba.emdeon_tpl_flag = '1'        then our_run_date
                                      when ba.emdeon_medicaid_flag = '1'   then our_run_date
                                      when ba.emdeon_medicare_flag = '1'   then our_run_date
                                      end),                                      
     emdeon_tpl_flag          =  ba.emdeon_tpl_flag, 
     emdeon_commercial_flag   =  ba.emdeon_commercial_flag,
     emdeon_medicare_flag     =  ba.emdeon_medicare_flag,
     emdeon_medicaid_flag     =  ba.emdeon_medicaid_flag
  FROM
     score_temp_insurance1_t ba
  WHERE
    (batch_insurance1_t.hid = ba.hid)
    AND
    (batch_insurance1_t.acctnum = ba.acctnum)
  ;

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH LOADING SCORES INTO batch_insurance1_t TABLE';


-- STEP2  UPDATE batch_status_insurance1_t WITH THE NUMBER UPDATED
-- UPDATE BATCH STATUS 

  UPDATE batch_status_insurance1_t
    SET
      batch_insurance1_scored  = v_status
    WHERE
      (batch_status_insurance1_t.last_run_date = v_batch_run_date and batch_status_insurance1_t.cust_id = v_cust_id);

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,5,'FINISH LOADING SCORES INTO batch_insurance1_t');



--
-- STEP 3 UPDATE batch_insurance1_t WITH PRIOR RESULTS
 
PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,6,'START UP batch_insurance1_t WITH PRIOR HITS');
RAISE NOTICE 'START UPDATING batch_insurance1_t TABLE WHEN INDIVIDUAL WAS A PRIOR HIT';


  UPDATE batch_insurance1_t
  SET
   emdeon_tpl_flag        = (CASE WHEN ba.emdeon_tpl_results >= 1 AND emdeon_tpl_flag in ('I','J')               THEN '2' 
                                  ELSE                                                                                emdeon_tpl_flag
                                  END),
   emdeon_commercial_flag = (CASE WHEN ba.emdeon_commercial_results >= 1 AND emdeon_commercial_flag in ('I','J') THEN '2' 
                                  ELSE                                                                                emdeon_commercial_flag
                                  END),
   emdeon_medicaid_flag   = (CASE WHEN ba.emdeon_medicaid_results >= 1 AND emdeon_medicaid_flag in ('I','J')     THEN '2' 
                                  ELSE                                                                                emdeon_medicaid_flag
                                  END),
   emdeon_medicare_flag   = (CASE WHEN ba.emdeon_medicare_results >= 1 AND emdeon_medicare_flag in ('I','J')     THEN '2' 
                                  ELSE                                                                                emdeon_medicare_flag
                                  END),
   emdeon_sent_date       = (CASE WHEN batch_insurance1_t.emdeon_tpl_flag in ('2','1','I','J')
                                       and
                                       ba.emdeon_tpl_results >= 1                                     THEN v_batch_run_date 
                                  WHEN batch_insurance1_t.emdeon_commercial_flag in ('2','1','I','J')
                                       and
                                       ba.emdeon_commercial_results >= 1                              THEN v_batch_run_date
                                  WHEN batch_insurance1_t.emdeon_medicaid_flag in ('2','1','I','J')
                                       and
                                       ba.emdeon_medicaid_results >= 1                                THEN v_batch_run_date
                                  WHEN batch_insurance1_t.emdeon_medicare_flag in ('2','1','I','J')
                                       and
                                       ba.emdeon_medicare_results >= 1                                THEN v_batch_run_date
                                  ELSE                                                                     batch_insurance1_t.emdeon_sent_date
                                  END)
  FROM
     (SELECT
         individual_matchkey,
         sum((case when emdeon_ins_type = 'M' THEN 1 ELSE 0 END))                                             as emdeon_medicare_results,
         sum((case when emdeon_ins_type = 'D' THEN 1 ELSE 0 END))                                             as emdeon_medicaid_results,
         sum((case when emdeon_ins_type = 'C' AND suppress_flag = 0 AND active_flag = 1 THEN 1 ELSE 0 END))   as emdeon_commercial_results,
         sum((case when emdeon_ins_type = 'T' AND suppress_flag = 0 AND active_flag = 1 THEN 1 ELSE 0 END))   as emdeon_tpl_results 
      FROM
         insurance2_results_t 
      WHERE
         quality <> 7
      GROUP BY
         individual_matchkey
     ) as ba
  WHERE
    (batch_insurance1_t.individual_matchkey = ba.individual_matchkey)
  ;


GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH UPDATING batch_insurance1_t TABLE WHEN INDIVIDUAL WAS PRIOR HIT';


SELECT INTO v_count sum(case when emdeon_sent_date IS NOT NULL THEN 1 else 0 end) from batch_insurance1_t;




--
-- STEP 4 UPDATE current_run field AND emdeon_medicare_fast flag

RAISE NOTICE 'START UPDATING current_run FIELD IN batch_insurance1_t';

update batch_insurance1_t
set
    current_commercial_run = ( CASE WHEN prior_commercial_run = 'Y' or emdeon_commercial_flag in ('1','2') THEN 'Y'
                                    ELSE                                                                        'N'
                                    END),
    current_tpl_run        = ( CASE WHEN prior_tpl_run = 'Y' or emdeon_tpl_flag in ('1','2')               THEN 'Y'
                                    ELSE                                                                        'N'
                                    END),
    current_medicaid_run   = ( CASE WHEN prior_medicaid_run = 'Y' or emdeon_medicaid_flag in ('1','2')     THEN 'Y'
                                    ELSE                                                                        'N'
                                    END),
    current_medicare_run   = ( CASE WHEN prior_medicare_run = 'Y' or emdeon_medicare_flag in ('1','2')     THEN 'Y'
                                    ELSE                                                                        'N'
                                    END),
    emdeon_medicare_fast = (CASE WHEN emdeon_medicare_flag <> '1' AND emdeon_medicare_flag <> '2' THEN '0'  
                                 ELSE                                                                  emdeon_medicare_fast 
                                 END)
;


RAISE NOTICE 'STOP UPDATING current_run FIELD IN batch_insurance1_t';




-- STEP5  UPDATE batch_status_insurance1_t WITH THE NUMBER ACCOUNTS CORRESPONDING TO INDIVIDUALS THAT WERE PRIOR HITS 
-- UPDATE BATCH STATUS

  UPDATE batch_status_insurance1_t
    SET
      batch_insurance1_prior_hits  = v_status,
      number_acct_selected_emdeon  = v_count
    WHERE
      (batch_status_insurance1_t.last_run_date = v_batch_run_date and batch_status_insurance1_t.cust_id = v_cust_id);


PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,6,'FINISH UP batch_insurance1_t WITH PRIOR HITS');



  return v_status;
END;
$$ LANGUAGE plpgsql;
